<h1 id="excel-操作">Excel 操作</h1>
<p>excel文件 实际是一堆xml文件的压缩包</p>
<p>Quick支持对老版xls的读操作，及最新版xlsx文件的读写操作</p>
<ul>
<li><a href="#xls-%E7%9B%B8%E5%85%B3%E5%AF%B9%E8%B1%A1">Excel - Xls</a></li>
<li><a href="#xlsx-%E7%9B%B8%E5%85%B3%E5%AF%B9%E8%B1%A1">Excel - Xlsx</a></li>
</ul>
<h3 id="xls-数据读取">xls 数据读取</h3>
<p><code>tab1.xls</code>文件内容如下：<br><a href="img/xls-src.png" data-fancybox="images"><img src="img/xls-src.png" alt="illustration"></a><br><a href="static/tab1.xls">源文件</a></p>
<pre><code class="hljs language-javascript">f = <span class="hljs-title function_">xls</span>(<span class="hljs-string">&quot;d:/tab1.xls&quot;</span>)
<span class="hljs-title function_">println</span>(<span class="hljs-string">&quot;xlsSheetNumber:&quot;</span>, f.<span class="hljs-title function_">sheetNumber</span>())
sheet = f.<span class="hljs-title function_">sheet</span>(<span class="hljs-number">0</span>)
<span class="hljs-title function_">println</span>(<span class="hljs-string">&quot;sheetName: &quot;</span>, sheet.<span class="hljs-title function_">name</span>())
<span class="hljs-title function_">println</span>(<span class="hljs-string">&quot;sheetRowNumber: &quot;</span>, sheet.<span class="hljs-title function_">rowNumber</span>())
row = sheet.<span class="hljs-title function_">row</span>(<span class="hljs-number">0</span>)
<span class="hljs-keyword">if</span> row {
    <span class="hljs-title function_">println</span>(<span class="hljs-string">&quot;headers:&quot;</span>)
    forv cell : row.<span class="hljs-title function_">cols</span>(<span class="hljs-params"></span>) {
        cell &amp;&amp; <span class="hljs-title function_">print</span>(cell.<span class="hljs-title function_">str</span>()+<span class="hljs-string">&quot;, &quot;</span>)
    }
}

<span class="hljs-title function_">println</span>(<span class="hljs-string">&quot;\ndata:&quot;</span>)
rows = sheet.<span class="hljs-title function_">rows</span>()
forv row : rows {
    cells = row.<span class="hljs-title function_">cols</span>()
    forv cell : cells {
        cell &amp;&amp; <span class="hljs-title function_">print</span>(cell.<span class="hljs-title function_">str</span>()+<span class="hljs-string">&quot;, &quot;</span>)
    }
    <span class="hljs-title function_">println</span>(<span class="hljs-string">&quot;\n=============================&quot;</span>)
}
</code></pre>
<p>输出</p>
<pre><code>xlsSheetNumber: 2
sheetName:  无名
sheetRowNumber:  4
headers:
name, age, sex, , , , qs, 
data:
zs, 9, m, , , zz, 
=============================
ls, 11, f, , yy, 
=============================
ww, 16, m, xx, 
=============================
</code></pre>
<h3 id="xlsx-读取">xlsx 读取</h3>
<p><code>tab2.xlsx</code>文件内容：
<a href="img/xlsx-src.png" data-fancybox="images"><img src="img/xlsx-src.png" alt="illustraction"></a><br><a href="static/tab2.xlsx">源文件</a>
方式1：</p>
<pre><code class="hljs language-javascript">f = <span class="hljs-title function_">xlsx</span>(<span class="hljs-string">&quot;d:/tab2.xlsx&quot;</span>)
sheet = f.<span class="hljs-title function_">sheet</span>(<span class="hljs-number">0</span>)
forv row : sheet.<span class="hljs-title function_">cellVals</span>(<span class="hljs-params"></span>) {
    <span class="hljs-title function_">printf</span>(<span class="hljs-string">&quot;[%v] &quot;</span>, row.<span class="hljs-title function_">size</span>())
    forv cell : row {
        <span class="hljs-title function_">print</span>(cell+<span class="hljs-string">&quot;, &quot;</span>)
    }
    <span class="hljs-title function_">println</span>(<span class="hljs-string">&quot;\n=====================&quot;</span>)
}
</code></pre>
<p>输出</p>
<pre><code>[6] name, age, sex, attr, , yyy, 
=====================
[7] zs, 9, m, a, , , 1, 
=====================
[8] ls, 11, f, ab, xxx, , , 22, 
=====================
[9] ww, 16, m, abc, , zzz, , , 333, 
=====================
</code></pre>
<p>方式2：</p>
<pre><code class="hljs language-javascript">f = <span class="hljs-title function_">xlsx</span>(<span class="hljs-string">&quot;d:/tab2.xlsx&quot;</span>)
sheet = f.<span class="hljs-title function_">sheet</span>(<span class="hljs-number">0</span>)
rows = sheet.<span class="hljs-title function_">rows</span>()
<span class="hljs-keyword">for</span> rows.<span class="hljs-title function_">next</span>(<span class="hljs-params"></span>) {
    cells = rows.<span class="hljs-title function_">cols</span>()
    <span class="hljs-title function_">println</span>(cells, cells.<span class="hljs-title function_">type</span>())
}
</code></pre>
<p>输出</p>
<pre><code>[&quot;name&quot;, &quot;age&quot;, &quot;sex&quot;, &quot;attr&quot;, &quot;&quot;, &quot;yyy&quot;] JSONArray
[&quot;zs&quot;, &quot;9&quot;, &quot;m&quot;, &quot;a&quot;, &quot;&quot;, &quot;&quot;, &quot;1&quot;] JSONArray
[&quot;ls&quot;, &quot;11&quot;, &quot;f&quot;, &quot;ab&quot;, &quot;xxx&quot;, &quot;&quot;, &quot;&quot;, &quot;22&quot;] JSONArray
[&quot;ww&quot;, &quot;16&quot;, &quot;m&quot;, &quot;abc&quot;, &quot;&quot;, &quot;zzz&quot;, &quot;&quot;, &quot;&quot;, &quot;333&quot;] JSONArray
</code></pre>
<h3 id="xlsx-写数据">xlsx 写数据</h3>
<pre><code class="hljs language-javascript">headers = [<span class="hljs-string">&quot;name&quot;</span>, <span class="hljs-string">&quot;sex&quot;</span>, <span class="hljs-string">&quot;age&quot;</span>, <span class="hljs-string">&quot;addr&quot;</span>]
data = [
{<span class="hljs-attr">name</span>:<span class="hljs-string">&quot;jerry&quot;</span>, <span class="hljs-attr">sex</span>:<span class="hljs-string">&quot;F&quot;</span>, <span class="hljs-attr">age</span>:<span class="hljs-number">9</span>, <span class="hljs-attr">addr</span>:<span class="hljs-string">&quot;America&quot;</span>},
{<span class="hljs-attr">name</span>:<span class="hljs-string">&quot;tom&quot;</span>, <span class="hljs-attr">sex</span>:<span class="hljs-string">&quot;M&quot;</span>, <span class="hljs-attr">age</span>:<span class="hljs-number">13</span>, <span class="hljs-attr">addr</span>:<span class="hljs-string">&quot;America&quot;</span>},
{<span class="hljs-attr">name</span>:<span class="hljs-string">&quot;changlie&quot;</span>, <span class="hljs-attr">sex</span>:<span class="hljs-string">&quot;M&quot;</span>, <span class="hljs-attr">age</span>:<span class="hljs-number">18</span>, <span class="hljs-attr">addr</span>:<span class="hljs-string">&quot;China&quot;</span>},
{<span class="hljs-attr">name</span>:<span class="hljs-string">&quot;zhangsan&quot;</span>, <span class="hljs-attr">sex</span>:<span class="hljs-string">&quot;F&quot;</span>, <span class="hljs-attr">age</span>:<span class="hljs-number">33</span>, <span class="hljs-attr">addr</span>:<span class="hljs-string">&quot;town&quot;</span>},
{<span class="hljs-attr">name</span>:<span class="hljs-string">&quot;fish&quot;</span>, <span class="hljs-attr">sex</span>:<span class="hljs-string">&quot;F&quot;</span>, <span class="hljs-attr">age</span>:<span class="hljs-number">2</span>, <span class="hljs-attr">addr</span>:<span class="hljs-string">&quot;The World&quot;</span>},
]

f = <span class="hljs-title function_">newXlsx</span>()
sheet = f.<span class="hljs-title function_">sheet</span>(<span class="hljs-number">0</span>)
<span class="hljs-comment">// 批量写数据</span>
sheet.<span class="hljs-title function_">setData</span>(headers, data)
<span class="hljs-comment">// 修改一个单元的数据</span>
<span class="hljs-comment">// 修改横坐标为B，纵坐标为2的单元格的值为&quot;boom!&quot;</span>
sheet.<span class="hljs-title function_">setCellValue</span>(<span class="hljs-string">&quot;B2&quot;</span>, <span class="hljs-string">&quot;boom!&quot;</span>)
f.<span class="hljs-title function_">saveAs</span>(<span class="hljs-string">&quot;d:/test.xlsx&quot;</span>)
<span class="hljs-title function_">println</span>(<span class="hljs-string">&quot;write successfully&quot;</span>)
</code></pre>
<p>写出的文件数据如下：<br><a href="img/xlsx-res.png" data-fancybox="images"><img src="img/xlsx-res.png" alt="illustration"></a></p>
<h1 id="xls-相关对象">Xls 相关对象</h1>
<h3 id="xls">Xls</h3>
<p>调用xls(path)后返回的对象类型</p>
<table>
<thead>
<tr>
<th>方法</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>sheetNumber()</td>
<td>返回sheet的数量</td>
</tr>
<tr>
<td>sheet(index)</td>
<td>返回一个类型为<a href="#XlsSheet">XlsSheet</a>的sheet对象</td>
</tr>
</tbody></table>
<h3 id="xlssheet">XlsSheet</h3>
<table>
<thead>
<tr>
<th>方法</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>name()</td>
<td>sheet名称</td>
</tr>
<tr>
<td>rowNumber()</td>
<td>有数据的行的数量</td>
</tr>
<tr>
<td>row(index)</td>
<td>返回索引为index的行对象，类型为<a href="#XlsRow">XlsRow</a></td>
</tr>
<tr>
<td>rows()</td>
<td>返回所有有数据的行对象（除了第一行），类型为<a href="#XlsRow">XlsRow</a> 数组</td>
</tr>
</tbody></table>
<h3 id="xlsrow">XlsRow</h3>
<table>
<thead>
<tr>
<th>方法</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>cols()</td>
<td>返回当前行的所有单元对象，类型为<a href="#XlsCell">XlsCell</a> 数组</td>
</tr>
<tr>
<td>col(index)</td>
<td>返回索引为index的单元对象，类型为<a href="#XlsCell">XlsCell</a></td>
</tr>
</tbody></table>
<h3 id="xlscell">XlsCell</h3>
<table>
<thead>
<tr>
<th>方法</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>getInt()</td>
<td>单元的int类型值</td>
</tr>
<tr>
<td>getFloat()</td>
<td>单元的float类型值</td>
</tr>
<tr>
<td>str()</td>
<td>单元的string类型值</td>
</tr>
<tr>
<td>getType()</td>
<td>单元的数据类型</td>
</tr>
</tbody></table>
<h1 id="xlsx-相关对象">Xlsx 相关对象</h1>
<h3 id="xlsx">Xlsx</h3>
<p>内置函数 <code>xlsx(path)</code>, <code>newXlsx()</code>返回的对象类型</p>
<table>
<thead>
<tr>
<th>方法</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>newSheet(name)</td>
<td>新建一个sheet,并返回该对象，类型为<a href="#XlsxSheet">XlsxSheet</a></td>
</tr>
<tr>
<td>setActiveSheet(index)</td>
<td>设置索引为index的对象为当前sheet(打开excel默认展示的sheet)</td>
</tr>
<tr>
<td>saveAs(path)</td>
<td>excel另存为</td>
</tr>
<tr>
<td>save()</td>
<td>保存excel修改</td>
</tr>
<tr>
<td>sheet(index)</td>
<td>返回索引为index的sheet, 类型为<a href="#XlsxSheet">XlsxSheet</a></td>
</tr>
</tbody></table>
<h3 id="xlsxsheet">XlsxSheet</h3>
<table>
<thead>
<tr>
<th>方法</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>setData(headers, data)</td>
<td>批量赋值</td>
</tr>
<tr>
<td>setCellValue(axis, value)</td>
<td>给指定坐标(e.g. B2)的单元格赋值字符串</td>
</tr>
<tr>
<td>rows()</td>
<td>返回当前sheet的<a href="#XlsxRows">XlsxRows</a>对象</td>
</tr>
<tr>
<td>cellVals()</td>
<td>获取所有含数据的单元格的值，类型为一个字符串二维数组</td>
</tr>
<tr>
<td>cellVal(axis)</td>
<td>获取指定坐标(e.g. B2)的单元格的字符串的值</td>
</tr>
</tbody></table>
<h3 id="xlsxrows">XlsxRows</h3>
<table>
<thead>
<tr>
<th>方法</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>next()</td>
<td>下一行是否存在数据，若是指针移向下一行，并返回true</td>
</tr>
<tr>
<td>cols()</td>
<td>返回当前行的所以单元格的数据(与<code>next()</code>结合使用), 数据为字符串类型</td>
</tr>
</tbody></table>
